package com.salon.server.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.TimeZone;

import org.apache.log4j.Logger;
import com.salon.client.ServiceException;
import com.salon.client.ServiceException.Code;
import com.salon.client.beans.ClientBean;
import com.salon.client.beans.IncomeBean;
import com.salon.client.beans.IncomeMaterialsBean;
import com.salon.client.beans.MasterBean;
import com.salon.client.beans.MaterialBean;
import com.salon.client.beans.PriceBean;
import com.salon.client.beans.ProcedureBean;
import com.salon.client.beans.WhereBean;
import com.salon.client.beans.WhereBean.Operator;
import com.salon.client.beans.WhereBean.Type;
import com.salon.server.Parameters;
import com.sun.istack.internal.Nullable;


public class DBUtils {
	public static final Logger LOG = Logger.getLogger(DBUtils.class);

	private static DBUtils instance = null;

	private static PreparedStatementFactory statementFactory = new LogableStatementFactory();
	// new StandardStatementFactory();

	private static DBConnector dbConnector;

	public static DBUtils getInstance() {
		if (instance == null) {
			instance = new DBUtils();
			TimeZone.setDefault(TimeZone.getTimeZone("Europe/Moscow"));
		}
		return instance;
	}

	public DBUtils() {
		String defaultURL = "jdbc:mysql://localhost:3306/salon?characterEncoding=cp1251";
		dbConnector = DBConnector.getInstance(Parameters
				.getDbConnectionString() != null ? Parameters
				.getDbConnectionString() : defaultURL, "root", "");
	}
	
	private String getWhereString(List<WhereBean> wheres) {
		return getWhereString(wheres, true);
	}
	
	private String getWhereString(List<WhereBean> wheres, boolean isAddWhere) {
		if ((wheres == null) || (wheres.size() == 0)) {
			return "";
		}

		String addTableName = "";
		
		String res = "";
		
		if (isAddWhere) {
			res += " where ";
		}
		else {
			res += " and ";
		}
		boolean isFirst = true;
		for (WhereBean bean : wheres) {
			String buf = "";
			if (!isFirst) {
				buf = " and ";
			} else {
				isFirst = false;
			}
			if (bean.getType() == Type.STRING) {
				if (bean.getOp() == Operator.LIKE) {
					buf += bean.getField() + " like '" + bean.getSVal() + "' ";
				}
			} else if (bean.getType() == Type.INTEGER) {
				buf += bean.getField() + bean.getOp().getSymbol()
						+ bean.getIVal();
			} else if (bean.getType() == Type.DATE) {
				buf += bean.getField() + bean.getOp().getSymbol() + "'"
						+ bean.getDValString() + "'";
			}
			res += buf;
		}
		return addTableName + res;
	}
	
	public void loadUser(String login, String password) throws ServiceException {
		Connection conn = null;
		PreparedStatement stmt = null;

		try {
			conn = dbConnector.getConnection();
			stmt = statementFactory.create(conn,
					"SELECT id FROM users WHERE login = ? AND passwd = ?");
			stmt.setString(1, login);
			stmt.setString(2, password);

			LOG.debug(stmt.toString());

			stmt.setFetchSize(1);
			ResultSet rs = stmt.executeQuery();
			if (!rs.next()) {
				// Такого пользователя нет
				throw new ServiceException(Code.AUTHFAILURE);
			}
			rs.close();
		} catch (SQLException ex) {
			// LOG.error("loadUser: " + ex.getMessage());
			throw new ServiceException(Code.INTERNAL);
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (conn != null)
					conn.close();
			} catch (Exception ex) {
			}
		}
	}

	public void registerNewUser(String login, String password, String name)
			throws ServiceException {
		Connection conn = null;
		PreparedStatement stmt = null;

		try {
			conn = dbConnector.getConnection();
			stmt = statementFactory.create(conn,
					"SELECT id FROM users WHERE login = ?");
			stmt.setString(1, login);

			// LOG.debug(stmt.toString());

			stmt.setFetchSize(1);
			ResultSet rs = stmt.executeQuery();

			if (rs.next()) {
				throw new ServiceException(Code.USERALREADYEXISTS);
			}
			rs.close();

			stmt = statementFactory.create(conn,
					"INSERT INTO users VALUES (NULL, ?, ?, ?)",
					Statement.RETURN_GENERATED_KEYS);
			stmt.setString(1, login);
			stmt.setString(2, password);
			stmt.setString(3, name);
			// LOG.debug(stmt.toString());
			stmt.executeUpdate();
			rs = stmt.getGeneratedKeys();
			if (rs == null || !rs.next()) {
				// LOG.error("Cannot get generated key.");
				throw new ServiceException(Code.INTERNAL);
			}
			rs.close();

		} catch (SQLException ex) {
			// LOG.error("registerNewUser: " + ex.getMessage());
			throw new ServiceException(Code.INTERNAL);
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (conn != null)
					conn.close();
			} catch (Exception ex) {
			}
		}
	}
	
	public List<ClientBean> loadClients(List<WhereBean> wheres)
	throws ServiceException {
		Connection conn = null;
		PreparedStatement stmt = null;
		List<ClientBean> list = new ArrayList<ClientBean>();
		try {
		
			conn = dbConnector.getConnection();
		
			stmt = statementFactory.create(conn,
					"SELECT * from client " + getWhereString(wheres));
			//stmt.setInt(1, employerId);
			LOG.debug(stmt.toString());
			ResultSet rs = stmt.executeQuery();
		
			while (rs.next()) {
				ClientBean bean = new ClientBean();
				bean.setId(rs.getInt("id"));
				bean.setName(rs.getString("name"));
				bean.setSurname(rs.getString("surname"));
				bean.setLastName(rs.getString("lastname"));
				bean.setAddress(rs.getString("address"));
				bean.setPhone(rs.getString("phone"));
				bean.setDob(rs.getTimestamp("dob"));
				bean.setDiscount(rs.getInt("discount"));
				
				list.add(bean);
			}
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
			LOG.error("loadClients: " + e.getMessage());
			throw new ServiceException(Code.INTERNAL);
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (conn != null)
					conn.close();
			} catch (Exception ex) {
			}
		}
		return list;
	}
	
	public void addClient(ClientBean bean) throws ServiceException {
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			conn = dbConnector.getConnection();
			stmt = statementFactory
					.create(
							conn,
							"INSERT INTO client(id, name, surname, lastname, address, phone, dob, discount) "
									+ "VALUES(NULL, ?, ?, ?, ?, ?, ?, ?)");

			int row = 0;
			stmt.setString(++row, bean.getName());
			stmt.setString(++row, bean.getSurname());
			stmt.setString(++row, bean.getLastName());
			stmt.setString(++row, bean.getAddress());
			stmt.setString(++row, bean.getPhone());
			
			Timestamp ts = convertDateToTimestamp(bean.getDob());

			if (ts != null) {
				stmt.setTimestamp(++row, ts);
			} else {
				stmt.setNull(++row, java.sql.Types.DATE);
			}
			stmt.setInt(++row, bean.getDiscount());
			
			LOG.debug(stmt.toString());
			stmt.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
			LOG.error("updateAccount: " + e.getMessage());
			throw new ServiceException(Code.INTERNAL);
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (conn != null)
					conn.close();
			} catch (Exception ex) {
			}
		}
	}
	
	public List<MasterBean> loadMasters(List<WhereBean> wheres)
	throws ServiceException {
		Connection conn = null;
		PreparedStatement stmt = null;
		List<MasterBean> list = new ArrayList<MasterBean>();
		try {
		
			conn = dbConnector.getConnection();
		
			stmt = statementFactory.create(conn,
					"SELECT * from master " + getWhereString(wheres));
			//stmt.setInt(1, employerId);
			LOG.debug(stmt.toString());
			ResultSet rs = stmt.executeQuery();
		
			while (rs.next()) {
				MasterBean bean = new MasterBean();
				bean.setId(rs.getInt("id"));
				bean.setName(rs.getString("name"));
				bean.setSurname(rs.getString("surname"));
				bean.setLastName(rs.getString("lastname"));
				bean.setAddress(rs.getString("address"));
				bean.setPhone(rs.getString("phone"));
				bean.setSalary(rs.getInt("salary"));
				bean.setPercent(rs.getInt("percent"));
				bean.setDob(rs.getTimestamp("dob"));
				list.add(bean);
				
				bean.setCurrentSalary(getMasterSalary(bean.getId(), bean.getSalary(), conn));
			}
			rs.close();
			
		} catch (Exception e) {
			e.printStackTrace();
			LOG.error("loadMasters: " + e.getMessage());
			throw new ServiceException(Code.INTERNAL);
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (conn != null)
					conn.close();
			} catch (Exception ex) {
			}
		}
		return list;
	}
	
	private float getMasterSalary(int id, int salary, Connection conn) throws ServiceException {
		PreparedStatement stmt = null;
		int percent = 0;
		float sum = 0;
		try {
			
			int procedSum = 0;
			Calendar cal = Calendar.getInstance();
			cal.set(Calendar.DAY_OF_MONTH, 1);
			cal.set(Calendar.HOUR_OF_DAY, 0);
			cal.set(Calendar.MINUTE, 0);
			cal.set(Calendar.SECOND, 0);
			cal.set(Calendar.MILLISECOND, 0);
			
			Date sDate = cal.getTime();
			Timestamp sts = convertDateToTimestamp(sDate);
			
			cal.add(Calendar.MONTH, 1);
			Date fDate = cal.getTime();
			Timestamp fts = convertDateToTimestamp(fDate);
			
			stmt = statementFactory.create(conn,
					"SELECT sum(cost) as sum, masterPercent " +
					"FROM procedures " +
					"WHERE master_id = ? and " +
					"date >= ? and " +
					"date <= ? and " +
					"status = true");
			
			stmt.setInt(1, id);
			stmt.setTimestamp(2, sts);
			stmt.setTimestamp(3, fts);
			LOG.debug(stmt.toString());
			ResultSet rs = stmt.executeQuery();
			
			if (rs.next()) {
				procedSum = (int)rs.getFloat("sum");
				percent = rs.getInt("masterPercent");
			}
			rs.close();
			sum = salary + (percent * procedSum) / 100;
			
		} catch (Exception e) {
			e.printStackTrace();
			LOG.error("getMasterSalary: " + e.getMessage());
			throw new ServiceException(Code.INTERNAL);
		} finally {
			try {
				if (stmt != null)
					stmt.close();
			} catch (Exception ex) {
			}
		}
		return sum;
	}
	
	public void addMaster(MasterBean bean) throws ServiceException {
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			conn = dbConnector.getConnection();
			stmt = statementFactory
					.create(
							conn,
							"INSERT INTO master(id, name, surname, lastname, address, phone, dob, salary, percent) "
									+ "VALUES(NULL, ?, ?, ?, ?, ?, ?, ?, ?)");

			int row = 0;
			stmt.setString(++row, bean.getName());
			stmt.setString(++row, bean.getSurname());
			stmt.setString(++row, bean.getLastName());
			stmt.setString(++row, bean.getAddress());
			stmt.setString(++row, bean.getPhone());
			
			
			Timestamp ts = convertDateToTimestamp(bean.getDob());

			if (ts != null) {
				stmt.setTimestamp(++row, ts);
			} else {
				stmt.setNull(++row, java.sql.Types.DATE);
			}
			stmt.setInt(++row, bean.getSalary());
			stmt.setInt(++row, bean.getPercent());
			
			LOG.debug(stmt.toString());
			stmt.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
			LOG.error("addMaster: " + e.getMessage());
			throw new ServiceException(Code.INTERNAL);
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (conn != null)
					conn.close();
			} catch (Exception ex) {
			}
		}
	}
	
	public List<MaterialBean> loadMaterials(List<WhereBean> wheres)
	throws ServiceException {
		Connection conn = null;
		PreparedStatement stmt = null;
		List<MaterialBean> list = new ArrayList<MaterialBean>();
		try {
		
			conn = dbConnector.getConnection();
		
			stmt = statementFactory.create(conn,
					"SELECT * from material " + getWhereString(wheres));
			//stmt.setInt(1, employerId);
			LOG.debug(stmt.toString());
			ResultSet rs = stmt.executeQuery();
		
			while (rs.next()) {
				MaterialBean bean = new MaterialBean();
				bean.setId(rs.getInt("id"));
				bean.setName(rs.getString("name"));
				bean.setDecs(rs.getString("description"));
				bean.setMade(rs.getString("made"));
				bean.setCode(rs.getString("code"));
				bean.setWeight(rs.getInt("weight"));
				bean.setCost(rs.getFloat("cost"));
				bean.setIsConted(rs.getBoolean("isCounted"));
				list.add(bean);
			}
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
			LOG.error("loadMaterials: " + e.getMessage());
			throw new ServiceException(Code.INTERNAL);
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (conn != null)
					conn.close();
			} catch (Exception ex) {
			}
		}
		return list;
	}
	
	public void addMaterial(MaterialBean bean) throws ServiceException {
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			conn = dbConnector.getConnection();
			stmt = statementFactory
					.create(
							conn,
							"INSERT INTO material(id, name, description, made, code, isCounted, weight, cost) "
									+ "VALUES(NULL, ?, ?, ?, ?, ?, ?, ?)");

			int row = 0;
			stmt.setString(++row, bean.getName());
			stmt.setString(++row, bean.getDecs());
			stmt.setString(++row, bean.getMade());
			stmt.setString(++row, bean.getCode());
			stmt.setBoolean(++row, bean.getIsConted());
			stmt.setInt(++row, bean.getWeight());
			stmt.setFloat(++row, bean.getCost());
			
			LOG.debug(stmt.toString());
			stmt.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
			LOG.error("addMaterial: " + e.getMessage());
			throw new ServiceException(Code.INTERNAL);
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (conn != null)
					conn.close();
			} catch (Exception ex) {
			}
		}
	}
	
	private Timestamp convertDateToTimestamp(Date date) {
		if (date == null) {
			return null;
		}
		return new Timestamp(date.getTime());
		
	}
	
	private java.sql.Date convertDateToSqlDate(Date date) {
		if (date == null) {
			return null;
		}
		return new java.sql.Date(date.getTime());
		
	}
	
	public void deleteItem(String table, int id) throws ServiceException {
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			conn = dbConnector.getConnection();
			stmt = statementFactory.create(conn,
					"delete from "+ table +" where id=?");

			int row = 0;
			stmt.setInt(++row, id);

			stmt.executeUpdate();

		} catch (SQLException ex) {
			LOG.error("deleteItem in" + table + ex.getMessage());
			throw new ServiceException(Code.INTERNAL);
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (conn != null)
					conn.close();
			} catch (Exception ex) {
			}
		}
	}
	
	public List<IncomeBean> loadIncomes()
	throws ServiceException {
		Connection conn = null;
		PreparedStatement stmt = null;
		List<IncomeBean> list = new ArrayList<IncomeBean>();
		try {
		
			conn = dbConnector.getConnection();
		
			stmt = statementFactory.create(conn,
					"SELECT *, " +
						"(SELECT sum(b.count * b.cost) " +
						"FROM income_material b " +
						"WHERE a.id = b.income_id) as sum " +
					"FROM income a");
			
			LOG.debug(stmt.toString());
			ResultSet rs = stmt.executeQuery();
		
			while (rs.next()) {
				IncomeBean bean = new IncomeBean();
				bean.setId(rs.getInt("id"));
				bean.setDate(rs.getDate("date"));
				bean.setSum(rs.getFloat("sum"));
				bean.setMaterials(loadIncomeMaterials(conn, bean.getId()));
				
				list.add(0, bean);
			}
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
			LOG.error("loadIncomes: " + e.getMessage());
			throw new ServiceException(Code.INTERNAL);
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (conn != null)
					conn.close();
			} catch (Exception ex) {
			}
		}
		return list;
	}
	
	public List<IncomeMaterialsBean> loadIncomeMaterials(Connection conn, int incomeId)
	throws ServiceException {
		PreparedStatement stmt = null;
		List<IncomeMaterialsBean> list = new ArrayList<IncomeMaterialsBean>();
		try {
		
			stmt = statementFactory.create(conn,
					"SELECT a.*, b.name, b.code " +
					"FROM income_material a, material b " +
					"WHERE a.income_id = ? and a.material_id = b.id ");
			stmt.setInt(1, incomeId);
			LOG.debug(stmt.toString());
			ResultSet rs = stmt.executeQuery();
			
			while (rs.next()) {
				IncomeMaterialsBean bean = new IncomeMaterialsBean();
				bean.setId(rs.getInt("a.id"));
				bean.setCost(rs.getFloat("a.cost"));
				bean.setCount(rs.getInt("a.count"));
				bean.setDesc(rs.getString("a.description"));
				bean.setName(rs.getString("b.name"));
				bean.setCode(rs.getString("b.code"));
				bean.setIncomeId(incomeId);
				bean.setMaterialId(rs.getInt("a.material_id"));
				list.add(bean);
			}
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
			LOG.error("loadIncomeMaterials: " + e.getMessage());
			throw new ServiceException(Code.INTERNAL);
		} finally {
			try {
				if (stmt != null)
					stmt.close();
			} catch (Exception ex) {
			}
		}
		return list;
	}
	
	public void addIncome(IncomeBean bean) throws ServiceException {
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			conn = dbConnector.getConnection();
			stmt = statementFactory
					.create(
							conn,
							"INSERT INTO income(id, date) "
									+ "VALUES(NULL, ?)");

			int row = 0;
			stmt.setTimestamp(++row, convertDateToTimestamp(bean.getDate()));
			
			
			LOG.debug(stmt.toString());
			stmt.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
			LOG.error("addIncome: " + e.getMessage());
			throw new ServiceException(Code.INTERNAL);
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (conn != null)
					conn.close();
			} catch (Exception ex) {
			}
		}
	}
	
	public IncomeMaterialsBean addIncomeMaterial(IncomeMaterialsBean bean) throws ServiceException {
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			conn = dbConnector.getConnection();
			stmt = statementFactory
					.create(
							conn,
							"INSERT INTO income_material(id, material_id, income_id, description, count, cost) "
									+ "VALUES(NULL, ?, ?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS);

			int row = 0;
			stmt.setInt(++row, bean.getMaterialId());
			stmt.setInt(++row, bean.getIncomeId());
			stmt.setString(++row, bean.getDesc());
			stmt.setInt(++row, bean.getCount());
			stmt.setFloat(++row, bean.getCost());
			LOG.debug(stmt.toString());
			stmt.executeUpdate();
			
			ResultSet rs = stmt.getGeneratedKeys();
			if (rs == null || !rs.next()) {
				LOG.error("addIncomeMaterial: Cannot get generated key.");
				throw new ServiceException(Code.INTERNAL);
			}
			bean.setId(rs.getInt(1));
			rs.close();
			
		} catch (Exception e) {
			e.printStackTrace();
			LOG.error("addIncomeMaterial: " + e.getMessage());
			bean = null;
			throw new ServiceException(Code.INTERNAL);
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (conn != null)
					conn.close();
			} catch (Exception ex) {
			}
		}
		return bean;
	}
	
	public List<PriceBean> loadPrice(List<WhereBean> wheres) 
	throws ServiceException {
		Connection conn = null;
		PreparedStatement stmt = null;
		List<PriceBean> list = new ArrayList<PriceBean>();
		try {
		
			conn = dbConnector.getConnection();
		
			stmt = statementFactory.create(conn,
					"SELECT * " +
					"FROM price " + getWhereString(wheres));
			
			LOG.debug(stmt.toString());
			ResultSet rs = stmt.executeQuery();
		
			while (rs.next()) {
				PriceBean bean = new PriceBean();
				bean.setId(rs.getInt("id"));
				bean.setName(rs.getString("name"));
				bean.setCost(rs.getFloat("cost"));
				bean.setList(loadUsedMaterials(conn, bean.getId()));
				
				list.add(0, bean);
			}
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
			LOG.error("loadPrice: " + e.getMessage());
			throw new ServiceException(Code.INTERNAL);
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (conn != null)
					conn.close();
			} catch (Exception ex) {
			}
		}
		return list;
	}
	
	public List<IncomeMaterialsBean> loadUsedMaterials(Connection conn, int priceId)
	throws ServiceException {
		PreparedStatement stmt = null;
		List<IncomeMaterialsBean> list = new ArrayList<IncomeMaterialsBean>();
		try {
		
			stmt = statementFactory.create(conn,
					"SELECT a.*, b.name, b.code " +
					"FROM price_details a, material b " +
					"WHERE a.price_id = ? and a.material_id = b.id ");
			stmt.setInt(1, priceId);
			LOG.debug(stmt.toString());
			ResultSet rs = stmt.executeQuery();
			
			while (rs.next()) {
				IncomeMaterialsBean bean = new IncomeMaterialsBean();
				bean.setId(rs.getInt("a.id"));
				bean.setCount(rs.getInt("a.count"));
				
				bean.setName(rs.getString("b.name"));
				bean.setCode(rs.getString("b.code"));
				bean.setIncomeId(priceId);
				bean.setMaterialId(rs.getInt("a.material_id"));
				
				list.add(bean);
			}
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
			LOG.error("loadUsedMaterials: " + e.getMessage());
			throw new ServiceException(Code.INTERNAL);
		} finally {
			try {
				if (stmt != null)
					stmt.close();
			} catch (Exception ex) {
			}
		}
		return list;
	}
	
	public void addPrice(PriceBean bean) throws ServiceException {
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			conn = dbConnector.getConnection();
			stmt = statementFactory
					.create(
							conn,
							"INSERT INTO price(id, name, cost) "
									+ "VALUES(NULL, ?, ?)");

			int row = 0;
			stmt.setString(++row, bean.getName());
			stmt.setFloat(++row, bean.getCost());
			
			LOG.debug(stmt.toString());
			stmt.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
			LOG.error("addPrice: " + e.getMessage());
			throw new ServiceException(Code.INTERNAL);
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (conn != null)
					conn.close();
			} catch (Exception ex) {
			}
		}
	}
	
	public IncomeMaterialsBean addUsedMaterial(IncomeMaterialsBean bean) throws ServiceException {
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			conn = dbConnector.getConnection();
			stmt = statementFactory
					.create(
							conn,
							"INSERT INTO price_details(id, material_id, price_id, count) "
									+ "VALUES(NULL, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS);

			int row = 0;
			stmt.setInt(++row, bean.getMaterialId());
			stmt.setInt(++row, bean.getIncomeId());
			stmt.setInt(++row, bean.getCount());
			
			LOG.debug(stmt.toString());
			stmt.executeUpdate();
			
			ResultSet rs = stmt.getGeneratedKeys();
			if (rs == null || !rs.next()) {
				LOG.error("addUsedMaterial: Cannot get generated key.");
				throw new ServiceException(Code.INTERNAL);
			}
			bean.setId(rs.getInt(1));
			rs.close();
			
		} catch (Exception e) {
			e.printStackTrace();
			LOG.error("addUsedMaterial: " + e.getMessage());
			bean = null;
			throw new ServiceException(Code.INTERNAL);
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (conn != null)
					conn.close();
			} catch (Exception ex) {
			}
		}
		return bean;
	}
	
	public void updateClient(ClientBean bean) throws ServiceException {
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			conn = dbConnector.getConnection();
			String sql = "UPDATE client SET " + "name = ?, "
					+ "surname = ?, " + "lastname = ?, "
					+ "address = ?, " + "phone = ?, "
					+ "dob = ?, discount = ? " + "WHERE id = ?";

			stmt = statementFactory.create(conn, sql);

			int row = 0;
			stmt.setString(++row, bean.getName());
			stmt.setString(++row, bean.getSurname());
			stmt.setString(++row, bean.getLastName());
			stmt.setString(++row, bean.getAddress());
			stmt.setString(++row, bean.getPhone());
			Timestamp ts = convertDateToTimestamp(bean.getDob());

			if (ts != null) {
				stmt.setTimestamp(++row, ts);
			} else {
				stmt.setNull(++row, java.sql.Types.DATE);
			}
			stmt.setInt(++row, bean.getDiscount());
			stmt.setInt(++row, bean.getId());

			LOG.debug(stmt.toString());
			stmt.executeUpdate();

		} catch (SQLException ex) {
			LOG.error("updateClient: " + ex.getMessage());
			throw new ServiceException(Code.INTERNAL);
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (conn != null)
					conn.close();
			} catch (Exception ex) {
			}
		}
	}
	
	public void updateMaster(MasterBean bean) throws ServiceException {
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			conn = dbConnector.getConnection();
			String sql = "UPDATE master SET " + "name = ?, "
					+ "surname = ?, " + "lastname = ?, "
					+ "address = ?, " + "phone = ?, "
					+ "dob = ?, "
					+ "salary = ?, " + "percent = ? "
					+ "WHERE id = ?";

			stmt = statementFactory.create(conn, sql);

			int row = 0;
			stmt.setString(++row, bean.getName());
			stmt.setString(++row, bean.getSurname());
			stmt.setString(++row, bean.getLastName());
			stmt.setString(++row, bean.getAddress());
			stmt.setString(++row, bean.getPhone());
			Timestamp ts = convertDateToTimestamp(bean.getDob());

			if (ts != null) {
				stmt.setTimestamp(++row, ts);
			} else {
				stmt.setNull(++row, java.sql.Types.DATE);
			}
			stmt.setInt(++row, bean.getSalary());
			stmt.setInt(++row, bean.getPercent());
			stmt.setInt(++row, bean.getId());

			LOG.debug(stmt.toString());
			stmt.executeUpdate();

		} catch (SQLException ex) {
			LOG.error("updateMaster: " + ex.getMessage());
			throw new ServiceException(Code.INTERNAL);
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (conn != null)
					conn.close();
			} catch (Exception ex) {
			}
		}
	}
	
	public void updateMaterial(MaterialBean bean) throws ServiceException {
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			conn = dbConnector.getConnection();
			String sql = "UPDATE material SET " + "name = ?, "
					+ "description = ?, " + "made = ?, "
					+ "code = ?, " + "isCounted = ?, "
					+ "weight = ?, cost = ? " + "WHERE id = ?";

			stmt = statementFactory.create(conn, sql);

			int row = 0;
			stmt.setString(++row, bean.getName());
			stmt.setString(++row, bean.getDecs());
			stmt.setString(++row, bean.getMade());
			stmt.setString(++row, bean.getCode());
			stmt.setBoolean(++row, bean.getIsConted());
			stmt.setInt(++row, bean.getWeight());
			stmt.setFloat(++row, bean.getCost());
			stmt.setInt(++row, bean.getId());

			LOG.debug(stmt.toString());
			stmt.executeUpdate();

		} catch (SQLException ex) {
			LOG.error("updateMaterial: " + ex.getMessage());
			throw new ServiceException(Code.INTERNAL);
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (conn != null)
					conn.close();
			} catch (Exception ex) {
			}
		}
	}
	
	public List<ProcedureBean> loadProcedures()
	throws ServiceException {
		PreparedStatement stmt = null;
		Connection conn = null;
		List<ProcedureBean> list = new ArrayList<ProcedureBean>();
		try {
			conn = dbConnector.getConnection();
			stmt = statementFactory.create(conn,
					"SELECT a.*, b.surname, " +
						"(SELECT c.surname FROM client c " +
						"WHERE c.id = a.client_id) as client_name, " +
						"(SELECT d.name FROM price d " +
						"WHERE d.id = a.price_id) as price_name " +
					"FROM procedures a, master b " +
					"WHERE a.master_id = b.id");
			LOG.debug(stmt.toString());
			ResultSet rs = stmt.executeQuery();
			
			while (rs.next()) {
				ProcedureBean bean = new ProcedureBean();
				bean.setId(rs.getInt("a.id"));
				bean.setClientId(rs.getInt("a.client_id"));
				bean.setPriceId(rs.getInt("a.price_id"));
				bean.setMasterId(rs.getInt("a.master_id"));
				
				bean.setCost(rs.getFloat("a.cost"));
				bean.setDesc(rs.getString("a.description"));
				bean.setDate(rs.getTimestamp("a.date"));
				
				bean.setMasterName(rs.getString("b.surname"));
				bean.setName(rs.getString("price_name"));
				bean.setClientName(rs.getString("client_name"));
				
				list.add(0, bean);
			}
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
			LOG.error("loadProcedures: " + e.getMessage());
			throw new ServiceException(Code.INTERNAL);
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (conn != null)
					conn.close();
			} catch (Exception ex) {
			}
		}
		return list;
	}
	

	public List<ProcedureBean> loadProcedures(Date date)
	throws ServiceException {
		PreparedStatement stmt = null;
		Connection conn = null;
		List<ProcedureBean> list = new ArrayList<ProcedureBean>();
		try {
			conn = dbConnector.getConnection();
			stmt = statementFactory.create(conn,
					"SELECT a.*, b.surname, " +
						"(SELECT CONCAT_WS(' ', c.surname, c.name, c.phone) FROM client c " +
						"WHERE c.id = a.client_id) as client_name, " +
						"(SELECT d.name FROM price d " +
						"WHERE d.id = a.price_id) as price_name " +
					"FROM procedures a, master b " +
					"WHERE " +
						"a.master_id = b.id and " +
						"a.date >= ? and " +
						"a.date < ?");
			
			int row = 0;
			
			Calendar d = Calendar.getInstance();
			d.setTime(date);
			
			stmt.setDate(++row, convertDateToSqlDate(d.getTime()));
			d.add(Calendar.DAY_OF_MONTH, 1);
			stmt.setDate(++row, convertDateToSqlDate(d.getTime()));
			
			LOG.debug(stmt.toString());
			ResultSet rs = stmt.executeQuery();
			
			while (rs.next()) {
				ProcedureBean bean = new ProcedureBean();
				bean.setId(rs.getInt("a.id"));
				bean.setClientId(rs.getInt("a.client_id"));
				bean.setMasterId(rs.getInt("a.master_id"));
				bean.setStatus(rs.getBoolean("a.status"));
				bean.setPercent(rs.getInt("a.masterPercent"));
				bean.setDiscount(rs.getInt("a.clientPercent"));
				bean.setCost(rs.getFloat("a.cost"));
				bean.setDesc(rs.getString("a.description"));
				bean.setDate(rs.getTimestamp("a.date"));
				
				bean.setMasterName(rs.getString("b.surname"));
				bean.setName(rs.getString("price_name"));
				bean.setClientName(rs.getString("client_name"));
				
				bean.setPrice(loadPriceForProcedures(conn, bean.getId()));
				
				list.add(0, bean);
			}
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
			LOG.error("loadProcedures: " + e.getMessage());
			throw new ServiceException(Code.INTERNAL);
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (conn != null)
					conn.close();
			} catch (Exception ex) {
			}
		}
		return list;
	}
	
	public List<PriceBean> loadPriceForProcedures(Connection conn, int procedureId)
	throws ServiceException {
		PreparedStatement stmt = null;
		List<PriceBean> list = new ArrayList<PriceBean>();
		try {
		
			stmt = statementFactory.create(conn,
					"SELECT a.*, b.name, b.cost " +
					"FROM procedures_price a, price b " +
					"WHERE a.procedures_id = ? and a.price_id = b.id ");
			stmt.setInt(1, procedureId);
			LOG.debug(stmt.toString());
			ResultSet rs = stmt.executeQuery();
			
			while (rs.next()) {
				PriceBean bean = new PriceBean();
				bean.setName(rs.getString("b.name"));
				bean.setCost(rs.getFloat("b.cost"));
				bean.setId(rs.getInt("a.price_id"));
				int id = rs.getInt("a.id");
				bean.setList(loadUsedMaterialsForProcedure(conn, id));
				
				list.add(bean);
			}
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
			LOG.error("loadPriceForProcedures: " + e.getMessage());
			throw new ServiceException(Code.INTERNAL);
		} finally {
			try {
				if (stmt != null)
					stmt.close();
			} catch (Exception ex) {
			}
		}
		return list;
	}
	
	public List<IncomeMaterialsBean> loadUsedMaterialsForProcedure(Connection conn, int priceId)
	throws ServiceException {
		PreparedStatement stmt = null;
		List<IncomeMaterialsBean> list = new ArrayList<IncomeMaterialsBean>();
		try {
		
			stmt = statementFactory.create(conn,
					"SELECT a.*, b.name, b.code, b.isCounted, b.weight " +
					"FROM procedures_details a, material b " +
					"WHERE a.procedures_price_id = ? and a.material_id = b.id ");
			stmt.setInt(1, priceId);
			LOG.debug(stmt.toString());
			ResultSet rs = stmt.executeQuery();
			
			while (rs.next()) {
				IncomeMaterialsBean bean = new IncomeMaterialsBean();
				bean.setId(rs.getInt("a.id"));
				bean.setCount(rs.getInt("a.count"));
				
				bean.setName(rs.getString("b.name"));
				bean.setCode(rs.getString("b.code"));
				bean.setWeight(rs.getInt("b.weight"));
				bean.setConted(rs.getBoolean("b.isCounted"));
				
				bean.setIncomeId(priceId);
				bean.setMaterialId(rs.getInt("a.material_id"));
				
				
				list.add(bean);
			}
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
			LOG.error("loadUsedMaterialsForProcedure: " + e.getMessage());
			throw new ServiceException(Code.INTERNAL);
		} finally {
			try {
				if (stmt != null)
					stmt.close();
			} catch (Exception ex) {
			}
		}
		return list;
	}
	
	public void updateProcedure(ProcedureBean bean) throws ServiceException {
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			conn = dbConnector.getConnection();
			String sql = "UPDATE procedures SET " + "client_id = ?, "
					+ "master_id = ?, " + "date = ?, "
					+ "cost = ?, " + "status = ?, "
					+ "masterPercent = ?, clientPercent = ? " + "WHERE id = ?";

			stmt = statementFactory.create(conn, sql);

			int row = 0;
			if (bean.getClient() != null) {
				stmt.setInt(++row, bean.getClient().getId());
			}
			else {
				stmt.setNull(++row, java.sql.Types.INTEGER);
			}
			stmt.setInt(++row, bean.getMasterId());
			if (bean.getDate() == null) {
				bean.setDate(new Date());
			}
			Timestamp ts = convertDateToTimestamp(bean.getDate());

			if (ts != null) {
				stmt.setTimestamp(++row, ts);
			} else {
				stmt.setNull(++row, java.sql.Types.DATE);
			}
			stmt.setFloat(++row, bean.getCost());
			stmt.setBoolean(++row, bean.getStatus());
			stmt.setInt(++row, bean.getPercent());
			stmt.setInt(++row, bean.getDiscount());
			stmt.setInt(++row, bean.getId());
			
			LOG.debug(stmt.toString());
			stmt.executeUpdate();
			
			deleteProcedure(conn, bean.getId());
			addProcedureForProcedure(conn, bean);

		} catch (SQLException ex) {
			LOG.error("updateMaterial: " + ex.getMessage());
			throw new ServiceException(Code.INTERNAL);
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (conn != null)
					conn.close();
			} catch (Exception ex) {
			}
		}
	}
	
	public void deleteProcedure(Connection conn, int procedureId) throws ServiceException {
		PreparedStatement stmt = null;
		try {
			stmt = statementFactory.create(conn,
					"delete from procedures_price where procedures_id = ?");

			int row = 0;
			stmt.setInt(++row, procedureId);

			stmt.executeUpdate();

		} catch (SQLException ex) {
			LOG.error("deleteProcedure" + ex.getMessage());
			throw new ServiceException(Code.INTERNAL);
		} finally {
			try {
				if (stmt != null)
					stmt.close();
			} catch (Exception ex) {
			}
		}
	}
	
	public ProcedureBean addProcedure(ProcedureBean bean) throws ServiceException {
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			conn = dbConnector.getConnection();
			stmt = statementFactory
					.create(
							conn,
							"INSERT INTO procedures(id, client_id, price_id, master_id, date, description, cost, status, masterPercent, clientPercent) "
									+ "VALUES(NULL, ?, ?, ?, ?, ?, ?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS);

			int row = 0;
			if (bean.getClient() != null) {
				stmt.setInt(++row, bean.getClient().getId());
			}
			else {
				stmt.setNull(++row, java.sql.Types.INTEGER);
			}
			
			stmt.setInt(++row, bean.getPriceId());
			stmt.setInt(++row, bean.getMasterId());
			if (bean.getDate() == null) {
				bean.setDate(new Date());
			}
			Timestamp ts = convertDateToTimestamp(bean.getDate());

			if (ts != null) {
				stmt.setTimestamp(++row, ts);
			} else {
				stmt.setNull(++row, java.sql.Types.DATE);
			}
			stmt.setString(++row, bean.getDesc());
			stmt.setFloat(++row, bean.getCost());
			stmt.setBoolean(++row, bean.getStatus());
			stmt.setInt(++row, bean.getPercent());
			stmt.setInt(++row, bean.getDiscount());
			
			LOG.debug(stmt.toString());
			
			stmt.executeUpdate();
			
			ResultSet rs = stmt.getGeneratedKeys();
			if (rs == null || !rs.next()) {
				LOG.error("addProcedure: Cannot get generated key.");
				throw new ServiceException(Code.INTERNAL);
			}
			bean.setId(rs.getInt(1));
			rs.close();
			
			addProcedureForProcedure(conn, bean);			
			
		} catch (Exception e) {
			e.printStackTrace();
			LOG.error("addProcedure: " + e.getMessage());
			bean = null;
			throw new ServiceException(Code.INTERNAL);
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (conn != null)
					conn.close();
			} catch (Exception ex) {
			}
		}
		return bean;
		
		
	}
	
	public void addProcedureForProcedure(Connection conn, ProcedureBean bean) throws ServiceException {
		if (bean.getPrice() == null) {
			return;
		}
		PreparedStatement stmt = null;
		try {

			for (PriceBean price : bean.getPrice()) {
				stmt = statementFactory
						.create(conn, "INSERT INTO procedures_price "
										+ " VALUES(NULL, ?, ?)", Statement.RETURN_GENERATED_KEYS);

				int row = 0;
				stmt.setInt(++row, price.getId());
				stmt.setInt(++row, bean.getId());
				LOG.debug(stmt.toString());
				
				stmt.executeUpdate();
				
				ResultSet rs = stmt.getGeneratedKeys();
				if (rs == null || !rs.next()) {
					LOG.error("addProcedure: Cannot get generated key.");
					throw new ServiceException(Code.INTERNAL);
				}
				price.setId(rs.getInt(1));
				rs.close();
				
				addUsedMaterialsForProcedure(conn, price);
			}
		} catch (Exception e) {
			e.printStackTrace();
			LOG.error("addProcedureForProcedure: " + e.getMessage());
			bean = null;
			throw new ServiceException(Code.INTERNAL);
		} finally {
			try {
				if (stmt != null)
					stmt.close();
			} catch (Exception ex) {
			}
		}
	}
	
	public void addUsedMaterialsForProcedure(Connection conn, PriceBean bean) throws ServiceException {
		if (bean.getList() == null) {
			return;
		}
		
		PreparedStatement stmt = null;
		try {

			for (IncomeMaterialsBean mat : bean.getList()) {
				stmt = statementFactory
						.create(conn, "INSERT INTO procedures_details "
										+ " VALUES(NULL, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS);

				int row = 0;
				stmt.setInt(++row, mat.getMaterialId());
				stmt.setInt(++row, bean.getId());
				stmt.setInt(++row, mat.getCount());
				
				LOG.debug(stmt.toString());
				
				stmt.executeUpdate();
				
				ResultSet rs = stmt.getGeneratedKeys();
				if (rs == null || !rs.next()) {
					LOG.error("addProcedure: Cannot get generated key.");
					throw new ServiceException(Code.INTERNAL);
				}
				mat.setId(rs.getInt(1));
				rs.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
			LOG.error("addUsedMaterialsForProcedure: " + e.getMessage());
			bean = null;
			throw new ServiceException(Code.INTERNAL);
		} finally {
			try {
				if (stmt != null)
					stmt.close();
			} catch (Exception ex) {
			}
		}
	}
	
	public List<Date> loadWorkDayForMaster(int masterId, Date sDate, Date fDate)
	throws ServiceException {
		Connection conn = null;
		PreparedStatement stmt = null;
		List<Date> list = new ArrayList<Date>();
		try {
		
			conn = dbConnector.getConnection();
		
			stmt = statementFactory.create(conn,
					"SELECT a.date " +
					"FROM workday a, workday_master b " +
					"WHERE " +
						"a.id = b.workday_id and " +
						"b.master_id = ? and " +
						"a.date >= ? and " +
						"a.date < ?");
			int row = 0;
			stmt.setInt(++row, masterId);
			stmt.setDate(++row, convertDateToSqlDate(sDate));
			stmt.setDate(++row, convertDateToSqlDate(fDate));
			LOG.debug(stmt.toString());
			
			ResultSet rs = stmt.executeQuery();
		
			while (rs.next()) {
				list.add(rs.getDate("a.date"));
			}
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
			LOG.error("loadWorkDayForMaster: " + e.getMessage());
			throw new ServiceException(Code.INTERNAL);
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (conn != null)
					conn.close();
			} catch (Exception ex) {
			}
		}
		return list;
	}
	
	public Map<Date, Integer> loadProcedureCostForMaster(int masterId, Date sDate, Date fDate)
	throws ServiceException {
		Connection conn = null;
		PreparedStatement stmt = null;
		Map<Date, Integer> result = new HashMap<Date, Integer>();
		try {
		
			conn = dbConnector.getConnection();
		
			stmt = statementFactory.create(conn,
					"SELECT date, sum(cost) as sum " +
					"FROM procedures " +
					"WHERE " +
						"master_id = ? and " +
						"date >= ? and " +
						"date < ? and " +
						"status = true " +
					"GROUP BY DAY(date)");
			int row = 0;
			stmt.setInt(++row, masterId);
			stmt.setDate(++row, convertDateToSqlDate(sDate));
			stmt.setDate(++row, convertDateToSqlDate(fDate));
			LOG.debug(stmt.toString());
			
			ResultSet rs = stmt.executeQuery();
		
			while (rs.next()) {
				result.put(rs.getDate("date"), new Integer((int)rs.getFloat("sum")));
			}
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
			LOG.error("loadProcedureCostForMaster: " + e.getMessage());
			throw new ServiceException(Code.INTERNAL);
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (conn != null)
					conn.close();
			} catch (Exception ex) {
			}
		}
		return result;
	}
	
	public void deleteWorkDay(int masterId, Date date) throws ServiceException {
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			conn = dbConnector.getConnection();
			stmt = statementFactory.create(conn,
					"DELETE FROM workday_master where " +
					"master_id = ? and workday_id = " +
					"(SELECT id FROM workday where date = ?)");
	
			int row = 0;
			stmt.setInt(++row, masterId);
			stmt.setDate(++row, convertDateToSqlDate(date));
			
			stmt.executeUpdate();
	
		} catch (SQLException ex) {
			LOG.error("deleteWorkDay:" + ex.getMessage());
			throw new ServiceException(Code.INTERNAL);
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (conn != null)
					conn.close();
			} catch (Exception ex) {
			}
		}
	}
		
	public void addWorkDay(int masterId, Date date) throws ServiceException {
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			int workdayId = -1;
			conn = dbConnector.getConnection();
			stmt = statementFactory
					.create(
							conn,
							"SELECT * FROM workday " +
							"WHERE date = ?");

			int row = 0;
			stmt.setDate(++row, convertDateToSqlDate(date));
			
			LOG.debug(stmt.toString());
			
			ResultSet rs = stmt.executeQuery();
			
			if (rs.next()) {
				workdayId = rs.getInt("id");
			}
			else {
				stmt = statementFactory
				.create(
						conn,
						"INSERT INTO workday(id, date) " +
						"VALUES(NULL, ?)", Statement.RETURN_GENERATED_KEYS);
				stmt.setDate(1, convertDateToSqlDate(date));
				
				LOG.debug(stmt.toString());
				
				stmt.executeUpdate();
				
				rs = stmt.getGeneratedKeys();
				if (rs == null || !rs.next()) {
					LOG.error("addWorkDay: Cannot get generated key.");
					throw new ServiceException(Code.INTERNAL);
				}
				workdayId = rs.getInt(1);
			}
			
			stmt = statementFactory
			.create(
					conn,
					"INSERT INTO workday_master(id, master_id, workday_id) " +
					"VALUES(NULL, ?, ?)");
			row = 0;
			stmt.setInt(++row, masterId);
			stmt.setInt(++row, workdayId);
			
			LOG.debug(stmt.toString());
			stmt.executeUpdate();
			
			rs.close();
			
		} catch (Exception e) {
			e.printStackTrace();
			LOG.error("addWorkDay: " + e.getMessage());
			throw new ServiceException(Code.INTERNAL);
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (conn != null)
					conn.close();
			} catch (Exception ex) {
			}
		}	
	}
	
	public List<MasterBean> getWorkingMastersForDay(Date date) throws ServiceException {
		Connection conn = null;
		PreparedStatement stmt = null;
		List<MasterBean> list = new ArrayList<MasterBean>();
		try {
		
			conn = dbConnector.getConnection();
		
			stmt = statementFactory.create(conn,
					"SELECT c.* " +
					"FROM workday a, workday_master b, master c " +
					"WHERE " +
						"a.id = b.workday_id and " +
						"a.date = ? and " + 
						"b.master_id = c.id ");
			int row = 0;
			stmt.setDate(++row, convertDateToSqlDate(date));
			LOG.debug(stmt.toString());
			
			ResultSet rs = stmt.executeQuery();
		
			while (rs.next()) {
				MasterBean bean = new MasterBean();
				bean.setId(rs.getInt("c.id"));
				bean.setName(rs.getString("c.name"));
				bean.setSurname(rs.getString("c.surname"));
				bean.setLastName(rs.getString("c.lastname"));
				bean.setAddress(rs.getString("c.address"));
				bean.setPhone(rs.getString("c.phone"));
				bean.setSalary(rs.getInt("c.salary"));
				bean.setPercent(rs.getInt("c.percent"));
				bean.setDob(rs.getTimestamp("c.dob"));
				list.add(bean);
			}
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
			LOG.error("getWorkingMasterForDay: " + e.getMessage());
			throw new ServiceException(Code.INTERNAL);
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (conn != null)
					conn.close();
			} catch (Exception ex) {
			}
		}
		return list;
	}
	
	public List<IncomeMaterialsBean> loadCurrentMaterials(List<WhereBean> wheres)
	throws ServiceException {
		Connection conn = null;
		PreparedStatement stmt = null;
		List<IncomeMaterialsBean> list = new ArrayList<IncomeMaterialsBean>();
		try {
		
			conn = dbConnector.getConnection();
		
			stmt = statementFactory.create(conn,
					"SELECT a.*, b.* FROM current_materials a, material b " +
					"where a.material_id = b.id " + getWhereString(wheres, false));
			LOG.debug(stmt.toString());
			ResultSet rs = stmt.executeQuery();
		
			while (rs.next()) {
				IncomeMaterialsBean bean = new IncomeMaterialsBean();
				bean.setId(rs.getInt("a.id"));
				bean.setCount(rs.getInt("a.count"));
				
				bean.setCode(rs.getString("b.code"));
				bean.setName(rs.getString("b.name"));
				bean.setWeight(rs.getInt("b.weight"));
				bean.setConted(rs.getBoolean("isCounted"));
				
				list.add(bean);
			}
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
			LOG.error("loadCurrentMaterials: " + e.getMessage());
			throw new ServiceException(Code.INTERNAL);
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (conn != null)
					conn.close();
			} catch (Exception ex) {
			}
		}
		return list;
	}
	
	public void calcCurrentMaterial()
	throws ServiceException {
		Connection conn = null;
		PreparedStatement stmt = null;
		Map<Integer, IncomeMaterialsBean> list = new HashMap<Integer, IncomeMaterialsBean>();
		try {
		
			conn = dbConnector.getConnection();
		
			stmt = statementFactory.create(conn,
					"SELECT b.*, sum(a.count*b.weight) as summ FROM income_material a, material b " +
					"WHERE a.material_id = b.id " +
					"GROUP BY a.material_id");
			LOG.debug(stmt.toString());
			ResultSet rs = stmt.executeQuery();
		
			while (rs.next()) {
				IncomeMaterialsBean bean = new IncomeMaterialsBean();
				bean.setMaterialId(rs.getInt("b.id"));
				bean.setCount(rs.getInt("summ"));
				
				list.put(bean.getMaterialId(), bean);
			}
			rs.close();
			
			stmt = statementFactory.create(conn,
					"SELECT a.material_id, sum(a.count) as summ FROM out_materials a " +
					"GROUP BY a.material_id");
			LOG.debug(stmt.toString());
			rs = stmt.executeQuery();
		
			while (rs.next()) {
				IncomeMaterialsBean bean = new IncomeMaterialsBean();
				bean.setMaterialId(rs.getInt("a.material_id"));
				bean.setCount(0 - rs.getInt("summ"));
				
				IncomeMaterialsBean oldbean = list.get(bean.getMaterialId());
				if (oldbean == null) {
					list.put(bean.getMaterialId(), bean);
				}
				else {
					oldbean.setCount(oldbean.getCount() + bean.getCount());
				}
			}
			rs.close();
			
			//used mat from proces
			stmt = statementFactory.create(conn,
					"SELECT a.material_id, sum(a.count) as summ FROM procedures_details a, " +
					"procedures_price b, procedures c " +
					"WHERE a.procedures_price_id = b.id and " +
					"b.procedures_id = c.id and " +
					"c.status = true " +
					"GROUP BY a.material_id");
			LOG.debug(stmt.toString());
			rs = stmt.executeQuery();
		
			while (rs.next()) {
				IncomeMaterialsBean bean = new IncomeMaterialsBean();
				bean.setMaterialId(rs.getInt("a.material_id"));
				bean.setCount(0 - rs.getInt("summ"));
				
				IncomeMaterialsBean oldbean = list.get(bean.getMaterialId());
				if (oldbean == null) {
					list.put(bean.getMaterialId(), bean);
				}
				else {
					oldbean.setCount(oldbean.getCount() + bean.getCount());
				}
			}
			rs.close();
			
			
			stmt = statementFactory.create(conn,
					"DELETE FROM current_materials");
			LOG.debug(stmt.toString());
			stmt.executeUpdate();
			
			stmt = statementFactory.create(conn,
					"ALTER TABLE current_materials AUTO_INCREMENT = 0 ");
			LOG.debug(stmt.toString());
			stmt.executeUpdate();
			
			stmt = statementFactory.create(conn,
					"INSERT INTO current_materials " +
					"values(NULL, ?, ?)");
			
			for (IncomeMaterialsBean bean : list.values()) {
				int row = 0;
				stmt.setInt(++row, bean.getMaterialId());
				stmt.setInt(++row, bean.getCount());
				LOG.debug(stmt.toString());
				stmt.executeUpdate();
			}

		} catch (Exception e) {
			e.printStackTrace();
			LOG.error("calcCurrentMaterial: " + e.getMessage());
			throw new ServiceException(Code.INTERNAL);
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (conn != null)
					conn.close();
			} catch (Exception ex) {
			}
		}
	}
	
	public List<IncomeMaterialsBean> loadOutcomeMaterials(List<WhereBean> wheres)
	throws ServiceException {
		Connection conn = null;
		PreparedStatement stmt = null;
		List<IncomeMaterialsBean> list = new ArrayList<IncomeMaterialsBean>();
		try {
		
			conn = dbConnector.getConnection();
		
			stmt = statementFactory.create(conn,
					"SELECT a.*, b.* FROM out_materials a, material b " +
					"where a.material_id = b.id " + getWhereString(wheres, false));
			LOG.debug(stmt.toString());
			ResultSet rs = stmt.executeQuery();
		
			while (rs.next()) {
				IncomeMaterialsBean bean = new IncomeMaterialsBean();
				bean.setId(rs.getInt("a.id"));
				bean.setCount(rs.getInt("a.count"));
				bean.setDesc(rs.getString("a.description"));
				bean.setDate(rs.getDate("a.add_date"));
				
				bean.setCode(rs.getString("b.code"));
				bean.setName(rs.getString("b.name"));
				bean.setWeight(rs.getInt("b.weight"));
				bean.setConted(rs.getBoolean("isCounted"));
				
				list.add(bean);
			}
			rs.close();
			
			List<IncomeMaterialsBean> outFromProc = loadOutcomeMaterialsFromProcedures(conn);
			list.addAll(outFromProc);
			Collections.sort(list);
			
		} catch (Exception e) {
			e.printStackTrace();
			LOG.error("loadOutcomeMaterials: " + e.getMessage());
			throw new ServiceException(Code.INTERNAL);
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (conn != null)
					conn.close();
			} catch (Exception ex) {
			}
		}
		return list;
	}
	
	public List<IncomeMaterialsBean> loadOutcomeMaterialsFromProcedures(Connection conn)
	throws ServiceException {
		PreparedStatement stmt = null;
		List<IncomeMaterialsBean> list = new ArrayList<IncomeMaterialsBean>();
		try {
			
			stmt = statementFactory.create(conn,
					"SELECT p.*, b.*, a.*, (select pr.name from price pr where c.price_id = pr.id) as pr_name " +
					"FROM procedures_details a, procedures_price c, " +
					"procedures p, material b " +
					"where a.material_id = b.id and " +
					"a.procedures_price_id = c.id and " +
					"c.procedures_id = p.id and " +
					"p.status = true");
			LOG.debug(stmt.toString());
			ResultSet rs = stmt.executeQuery();
		
			while (rs.next()) {
				IncomeMaterialsBean bean = new IncomeMaterialsBean();
				bean.setId(rs.getInt("a.id"));
				bean.setCount(rs.getInt("a.count"));
				String desc = "Списано для процедуры " + rs.getString("pr_name");
				bean.setDesc(desc);
				bean.setDate(rs.getTimestamp("p.date"));
				
				bean.setCode(rs.getString("b.code"));
				bean.setName(rs.getString("b.name"));
				bean.setWeight(rs.getInt("b.weight"));
				bean.setConted(rs.getBoolean("isCounted"));
				
				list.add(bean);
			}
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
			LOG.error("loadOutcomeMaterialsFromProcedures: " + e.getMessage());
			throw new ServiceException(Code.INTERNAL);
		} finally {
			try {
				if (stmt != null)
					stmt.close();
			} catch (Exception ex) {
			}
		}
		return list;
	}
	
	public void addOutcomeMaterial(IncomeMaterialsBean bean) throws ServiceException {
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			conn = dbConnector.getConnection();
			stmt = statementFactory
					.create(
							conn,
							"INSERT INTO out_materials "
									+ "VALUES(NULL, ?, ?, ?, ?)");

			int row = 0;
			stmt.setInt(++row, bean.getMaterialId());
			stmt.setInt(++row, bean.getCount());
			stmt.setTimestamp(++row, convertDateToTimestamp(bean.getDate()));
			stmt.setString(++row, bean.getDesc());
			
			LOG.debug(stmt.toString());
			stmt.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
			LOG.error("addOutcomeMaterial: " + e.getMessage());
			throw new ServiceException(Code.INTERNAL);
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (conn != null)
					conn.close();
			} catch (Exception ex) {
			}
		}
	}
}